{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "6c632d0e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data inserted successfully\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "import mysql.connector\n",
    "from mysql.connector import Error\n",
    "\n",
    "url = 'https://mock.jsont.run/6zA7NH6ciqxNxGYzKO-Zx'\n",
    "response = requests.get(url)\n",
    "\n",
    "if response.status_code == 200:\n",
    "    # 解析json数据\n",
    "    data = response.json()\n",
    "else:\n",
    "    print(\"Failed to retrieve data:\", response.status_code)\n",
    "\n",
    "# 初始化一个字典来存储每个国家的票房总数\n",
    "country_box_office = {}\n",
    "ratings = {'3.0-3.5': 0, '9.0-9.5': 0, '无评分': 0}\n",
    "\n",
    "# 遍历数据，累加每个国家的票房\n",
    "for movie in data['data']:\n",
    "    country = movie['制片地区']   # 汉国,战国,中国\n",
    "    score = movie['评分']\n",
    "    gross = int(movie['票房'])  # 将票房转换为整数\n",
    "    # 判断country是否在字典中\n",
    "    if country in country_box_office:\n",
    "        # 以国家为键去统计评分\n",
    "        # country_box_office['汉国'] = country_box_office[country]\n",
    "        country_box_office[country] += gross\n",
    "    else:\n",
    "        country_box_office[country] = gross\n",
    "    # 在对应的评分统计票房\n",
    "    \n",
    "    if score == '-':\n",
    "        ratings['无评分'] += gross\n",
    "    else:\n",
    "        score_value = float(score)\n",
    "        if 3.0 <= score_value <= 3.5:\n",
    "            ratings['3.0-3.5'] += gross\n",
    "        elif 9.0 <= score_value <= 9.5:\n",
    "            ratings['9.0-9.5'] += gross\n",
    "\n",
    "\n",
    "# 根据票房总数对国家进行排序,因为字典是无序的,所以需要使用items转换\n",
    "# key 第一个x是country_box_office.items(),x[1]是循环每一个item的第二个索引值, 采用降序\n",
    "sorted_countries = sorted(country_box_office.items(), key=lambda x: x[1], reverse=True)\n",
    "# 获取票房总数最高的三个国家\n",
    "top_3_countries = sorted_countries[:3]\n",
    "\n",
    "# 数据库连接配置\n",
    "# config = {\n",
    "#     'user':'yingdao',\n",
    "#     'password':'9527',\n",
    "#     'host':'43.143.30.32',\n",
    "#     'database':'ydtest',\n",
    "#     'raise_on_warnings':True\n",
    "# }\n",
    "config = {\n",
    "    'user':'root',\n",
    "    'password':'123456',\n",
    "    'host':'localhost',\n",
    "    'database':'webstore',\n",
    "    'raise_on_warnings':True\n",
    "}\n",
    "\n",
    "# 连接到数据库\n",
    "conn = mysql.connector.connect(**config)\n",
    "try:\n",
    "    if conn.is_connected():\n",
    "        cursor = conn.cursor()\n",
    "        # 写入国家票房数据\n",
    "        for country,gross in top_3_countries:\n",
    "            query = \"INSERT INTO film (submiter, country, gross) VALUES (%s, %s, %s)\"\n",
    "            cursor.execute(query, ('lijiangyu', country, gross))\n",
    "            \n",
    "        for rating, gross in ratings.items():\n",
    "            query = \"INSERT INTO film (submiter, country, gross) VALUES (%s, %s, %s)\"\n",
    "            cursor.execute(query,(\"lijiangyu\",rating, gross))\n",
    "        conn.commit()\n",
    "        print(\"Data inserted successfully\")\n",
    "except Error as e:\n",
    "    print(\"Error while connecting to MySQL\", e)\n",
    "finally:\n",
    "    # 最后执行,如果数据库链接,便关闭\n",
    "    if conn.is_connected():\n",
    "        cursor.close()\n",
    "        conn.close()\n",
    "        print(\"MySQL connection is closed\")\n",
    "\n",
    "# # 打印结果\n",
    "# print(\"票房总数最高的三个国家:\")\n",
    "# for country, gross in top_3_countries:\n",
    "#     print(f\"{country}:{gross}\")\n",
    "# print(top_3_countries)\n",
    "# print(ratings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "45101ce6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "票房总数最高的三个国家:\n",
      "中国:1176140\n",
      "汉国:577524\n",
      "战国:569456\n",
      "[('中国', 1176140), ('汉国', 577524), ('战国', 569456)]\n",
      "dict_items([('3.0-3.5', 1701930), ('9.0-9.5', 570621), ('无评分', 592436)])\n",
      "3.0-3.5:1701930\n",
      "9.0-9.5:570621\n",
      "无评分:592436\n"
     ]
    }
   ],
   "source": [
    "# 假设data_dict是从API获取的数据字典\n",
    "data_dict = {\n",
    "    'data': [\n",
    "        {'上映年份': '2021', '制片地区': '汉国', '导演': '陈凯歌 *', '提交人': 'zhchl@fckj', '电影名称': '长津湖', '票房': '577524', '评分': '3.1'},\n",
    "        {'上映年份': '2017', '制片地区': '战国', '导演': '吴京', '提交人': 'zhchl@fckj', '电影名称': '战狼2', '票房': '569456', '评分': '3.3'},\n",
    "        {'上映年份': '2021', '制片地区': '韩国', '导演': '贾玲', '提交人': 'zhchl@fckj', '电影名称': '你好，李焕英', '票房': '541372', '评分': '无评分'},\n",
    "        {'上映年份': '2019', '制片地区': '中国', '导演': '饺子', '提交人': 'zhchl@fckj', '电影名称': '哪吒之魔童降世', '票房': '503557', '评分': '3.5'},\n",
    "        {'上映年份': '2019', '制片地区': '中国', '导演': '郭帆', '提交人': 'zhchl@fckj', '电影名称': '流浪地球', '票房': '468814', '评分': '9.0'},\n",
    "        {'上映年份': '2014', '制片地区': '中国', '导演': '姜文', '提交人': 'zhchl@fckj', '电影名称': '一步之遥', '票房': '51419', '评分': '9.1'},\n",
    "        {'上映年份': '2016', '制片地区': '未知', '导演': '费格尔·雷利 *', '提交人': 'zhchl@fckj', '电影名称': '愤怒的小鸟', '票房': '51393', '评分': '3.2'}, \n",
    "        {'上映年份': '2015', '制片地区': '中国', '导演': '韩延', '提交人': 'zhchl@fckj', '电影名称': '滚蛋吧！肿瘤君', '票房': '51064', '评分': '无评分'}, \n",
    "        {'上映年份': '2018', '制片地区': '中国', '导演': '韩延', '提交人': 'zhchl@fckj', '电影名称': '动物世界', '票房': '50898', '评分': '7.2'}, \n",
    "        {'上映年份': '2012', '制片地区': '美国', '导演': '巴里·索南菲尔德', '提交人': 'zhchl@fckj', '电影名称': '黑衣人3', '票房': '50415', '评分': '7.7'}, \n",
    "        {'上映年份': '2021', '制片地区': '中国', '导演': '黄建新 / 郑大圣', '提交人': 'zhchl@fckj', '电影名称': '1921', '票房': '50388', '评分': '9.4'}\n",
    "    ]\n",
    "}\n",
    "\n",
    "# 初始化一个字典来存储每个国家的票房总数\n",
    "country_box_office = {}\n",
    "ratings = {'3.0-3.5': 0, '9.0-9.5': 0, '无评分': 0}\n",
    "\n",
    "# 遍历数据，累加每个国家的票房\n",
    "for movie in data_dict['data']:\n",
    "    country = movie['制片地区']   # 汉国,战国,中国\n",
    "    score = movie['评分']\n",
    "    gross = int(movie['票房'])  # 将票房转换为整数\n",
    "    # 判断country是否在字典中\n",
    "    if country in country_box_office:\n",
    "        # 以国家为键去统计评分\n",
    "        # country_box_office['汉国'] = country_box_office[country]\n",
    "        country_box_office[country] += gross\n",
    "    else:\n",
    "        country_box_office[country] = gross\n",
    "    # 在对应的评分统计票房\n",
    "    \n",
    "    if score == '无评分':\n",
    "        ratings['无评分'] += gross\n",
    "    else:\n",
    "        score_value = float(score)\n",
    "        if 3.0 <= score_value <= 3.5:\n",
    "            ratings['3.0-3.5'] += gross\n",
    "        elif 9.0 <= score_value <= 9.5:\n",
    "            ratings['9.0-9.5'] += gross\n",
    "\n",
    "\n",
    "# 根据票房总数对国家进行排序,因为字典是无序的,所以需要使用items转换\n",
    "# key 第一个x是country_box_office.items(),x[1]是循环每一个item的第二个索引值, 采用降序\n",
    "sorted_countries = sorted(country_box_office.items(), key=lambda x: x[1], reverse=True)\n",
    "# # 获取票房总数最高的三个国家\n",
    "top_3_countries = sorted_countries[:3]\n",
    "\n",
    "# # 打印结果\n",
    "print(\"票房总数最高的三个国家:\")\n",
    "for country, gross in top_3_countries:\n",
    "    print(f\"{country}:{gross}\")\n",
    "print(top_3_countries)\n",
    "print(ratings.items())\n",
    "for rating, gross in ratings.items():\n",
    "    print(f\"{rating}:{gross}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
